﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CarLoanSystem.Entity;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class SequesterInfoDAL : DBBase
    {
        public static void Insert(SequesterItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[SEQUESTER]
                                                       ([LOAN_ID]
                                                       ,[CAR_PRICE]
                                                       ,[SEQUESTER_DATE]
                                                       ,[SEQUESTER_STATUS])
                                                 VALUES
                                                      ( @LOAN_ID
                                                       ,@CAR_PRICE
                                                       ,@SEQUESTER_DATE
                                                       ,@SEQUESTER_STATUS)", DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@CAR_PRICE", item.CAR_PRICE);
                command.Parameters.AddWithValue("@SEQUESTER_DATE", item.SEQUESTER_DATE);
                command.Parameters.AddWithValue("@SEQUESTER_STATUS", item.SEQUESTER_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void UpdateSequesterStatus(SequesterItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[SEQUESTER]
                                               SET [SEQUESTER_STATUS] = @SEQUESTER_STATUS,
                                                [SEQUESTER_DATE] = @SEQUESTER_DATE,
                                                [CAR_PRICE] = @CAR_PRICE
                                             WHERE ([SEQUESTER_ID] = @SEQUESTER_ID)", DBBase.con);
                command.Parameters.AddWithValue("@SEQUESTER_ID", item.SEQUESTER_ID);
                command.Parameters.AddWithValue("@SEQUESTER_STATUS", item.SEQUESTER_STATUS);
                command.Parameters.AddWithValue("@SEQUESTER_DATE", item.SEQUESTER_DATE);
                command.Parameters.AddWithValue("@CAR_PRICE", item.CAR_PRICE);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static SequesterItem LoadSequester(int LOAN_ID)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [SEQUESTER_ID]
                                                      ,[LOAN_ID]
                                                      ,[CAR_PRICE]
                                                      ,[SEQUESTER_DATE]
                                                      ,[SEQUESTER_STATUS]
                                                  FROM [CAR_LOAN].[dbo].[SEQUESTER]
                                                  where [LOAN_ID] = @LOAN_ID ", DBBase.con);
                command.Parameters.AddWithValue("@LOAN_ID", LOAN_ID);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    SequesterItem item = new SequesterItem();
                    item.SEQUESTER_ID = Convert.ToInt32(dt.Rows[0]["SEQUESTER_ID"].ToString());
                    item.LOAN_ID = Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                    if (!dt.Rows[0]["CAR_PRICE"].ToString().Equals(""))
                    {
                        item.CAR_PRICE = Convert.ToInt32(dt.Rows[0]["CAR_PRICE"].ToString());
                    }
                    item.SEQUESTER_STATUS = dt.Rows[0]["SEQUESTER_STATUS"].ToString();
                    item.SEQUESTER_DATE = Convert.ToDateTime(dt.Rows[0]["SEQUESTER_DATE"].ToString());
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }
    }
}
